const Koa = require('koa');
const Bodyparser = require('koa-bodyparser');
const loadRouters = require('require-directory');
const logger = require('koa-logger');
const koaBody = require('koa-body');
const Router = require('koa-router');
const mysql = require('mysql2/promise');
const fs = require('fs')
const path = require('path')
const mime=require('mime-types')
const https = require('https'); 
const { Server } = require('http');
const app = new Koa();
const router=new Router();
const bodyparser=new Bodyparser();

// const connection= mysql.createPool({
//     host:'localhost',
//     user:'root',
//     password:'zhaofuyu',
//     database:'test'
//   })
  async function query(sql) {
    //连接mysql2
    const mysql = require('mysql2/promise');
    // 创建连接
    const connection = await mysql.createConnection({host:'101.34.58.111', user: 'root', password:'shanganapp',database: '上岸'});
    // 执行sql语句
    const [rows, fields] = await connection.execute(sql);
    return rows;
  }


//设置跨域资源请求的问题
app.use(async (ctx,next) => {
  ctx.set('Access-Control-Allow-Origin', '*');
  ctx.set('Access-Control-Allow-Headers','Content-Type, Content-Length, Authorization, Accept, X-Requested-With,yourHeaderFeild'
    );
  ctx.set('Access-Control-Allow-Methods', 'PUT, POST, GET, DELETE');
  if(ctx.method == 'OPTIONS'){
    ctx.body = 200;
  }else{
    await next();  }
})
// app.use(logger());
app.use(koaBody({
  multipart:true
}))


// loadRouters(module,'./routes',{visit:(r) => {
//   app.use(r.routes());
// }})


//注册
router.put('/user/register',async(ctx,next)=>{
  console.log(ctx.request.body)
  let sql=`insert into user (user_number,user_pwd,user_img,user_name,user_major) values (${"'"+ctx.request.body.account+"'"},${"'"+ctx.request.body.pwd+"'"},${"'"+ctx.request.body.img+"'"},${"'"+ctx.request.body.name+"'"},${"'"+ctx.request.body.college+"'"})`;
  await query(sql);
})

//登录
router.get('/user/login',async (ctx,next) => {
  let sql = `select * from user`;
  let res = await query(sql);
  ctx.body =JSON.stringify(res);
})


//参考书
router.get('/book/math',async (ctx,next) => {
    let sql = `select * from book where subjects="数学"`;
    let res = await query(sql);
    ctx.body =JSON.stringify(res);
  })

  router.get('/book/English',async (ctx,next) => {
    let sql = `select * from book where subjects="英语"`;
    let res = await query(sql);
    ctx.body =JSON.stringify(res);
  }) 

  router.get('/book/politics',async (ctx,next) => {
    let sql = `select * from book where subjects="政治"`;
    let res = await query(sql);
    ctx.body =JSON.stringify(res);
  })

  router.get('/book/computer',async (ctx,next) => {
    let sql = `select * from book where subjects="计算机学科基础"`;
    let res = await query(sql);
    ctx.body =JSON.stringify(res);
  })


//百科

//考研常识
router.get('/exam/knowledge',async (ctx,next)=>{
  let sql=`select title,content,brief,readnum from examknowledge`;
  let res=await query(sql);
  ctx.body=JSON.stringify(res)
})

//考研政策
router.get('/exam/policy',async (ctx,next)=>{
  let sql=`select title,content,brief,readnum from exampolicy`;
  let res=await query(sql);
  ctx.body=JSON.stringify(res)
})

//选择院校
router.get('/exam/choose',async (ctx,next)=>{
  let sql=`select title,content,brief,readnum from examchoose`;
  let res=await query(sql);
  ctx.body=JSON.stringify(res)
})

//备考指南
router.get('/exam/guide',async (ctx,next)=>{
  let sql=`select title,content,brief,readnum from examguide`;
  let res=await query(sql);
  ctx.body=JSON.stringify(res)
 
})
//阅读量
router.put('/exam/readnum',async (ctx,next)=>{

  if(ctx.request.body.tab=='know'){
    let sql=`update examknowledge set readnum=${ctx.request.body.num+1} where title=${"'"+ctx.request.body.title+"'"}`;
    await query(sql);
    let sql2=`select title,content,brief,readnum from examknowledge`;
    let res=await query(sql2);
    ctx.body=JSON.stringify(res)
  }else if(ctx.request.body.tab=='policy'){
    let sql=`update exampolicy set readnum=${ctx.request.body.num+1} where title=${"'"+ctx.request.body.title+"'"}`;
    await query(sql);
    let sql2=`select title,content,brief,readnum from exampolicy`;
    let res=await query(sql2);
    ctx.body=JSON.stringify(res)
  }else if(ctx.request.body.tab=='college'){
    let sql=`update examchoose set readnum=${ctx.request.body.num+1} where title=${"'"+ctx.request.body.title+"'"}`;
    await query(sql);
    let sql2=`select title,content,brief,readnum from examchoose`;
    let res=await query(sql2);
    ctx.body=JSON.stringify(res)
  }else{ 
    let sql=`update examguide set readnum=${ctx.request.body.num+1} where title=${"'"+ctx.request.body.title+"'"}`;
    await query(sql);
    let sql2=`select title,content,brief,readnum from examguide`;
    let res=await query(sql2);
    ctx.body=JSON.stringify(res)
  }
  
})

//院校

//获取院校
router.get('/school/name',async (ctx,next)=>{
  let list=[
    {city:'上海'},
    {city:'北京'},
    {city:'天津'},
    {city:'安徽'},
    {city:'河北'},
    {city:'辽宁'},
    {city:'黑龙江'},
    {city:'吉林'},
  ];
  
  let sql1=`select distinct school_name from schoolscore where school_city="上海"`;
  list[0].school=await query(sql1)

  let sql2=`select distinct school_name from schoolscore where school_city="北京"`;
  list[1].school=await query(sql2)

  let sql3=`select distinct school_name from schoolscore where school_city="安徽"`;
  list[2].school=await query(sql3)

  let sql4=`select distinct school_name from schoolscore where school_city="天津"`;
  list[3].school=await query(sql4)

  let sql5=`select distinct school_name from schoolscore where school_city="河北"`;
  list[4].school=await query(sql5)

  let sql6=`select distinct school_name from schoolscore where school_city="辽宁"`;
  list[5].school=await query(sql6)

  let sql7=`select distinct school_name from schoolscore where school_city="黑龙江"`;
  list[6].school=await query(sql7)

  let sql8=`select distinct school_name from schoolscore where school_city="吉林"`;
  list[7].school=await query(sql8)
  ctx.body=JSON.stringify(list)
})

//招生简章
router.post('/school/recruit/',async (ctx,next)=>{
  let sql=`select * from schoolrecruit where school_name=${"'"+ctx.request.body.name+"'"}`;
  let res=await query(sql);
  ctx.body=JSON.stringify(res);
})

router.put('/school/readnum',async (ctx,next)=>{
  let sql=`update schoolrecruit set readnum=${ctx.request.body.num+1} where school_name=${"'"+ctx.request.body.name+"'"}`;
  await query(sql);
  let sql2=`select * from schoolrecruit`;
  let res=await query(sql2);
  ctx.body=JSON.stringify(res)
})
//考研分数线
router.post('/school/score/',async (ctx,next)=>{
  let sql=`select * from schoolscore where school_name=${"'"+ctx.request.body.name+"'"}`;
  let res=await query(sql);
  ctx.body=JSON.stringify(res);
})

//考研报录比
router.post('/school/number/',async (ctx,next)=>{
  let sql=`select * from schoolnumber where school_name=${"'"+ctx.request.body.name+"'"}`;
  let res=await query(sql);
  ctx.body=JSON.stringify(res);
})


//浏览历史
router.put('/user/examhistory',async (ctx,next)=>{
  let sql=`insert ignore into userexamhistory (history_brief,history_title,history_content,readnum,user_number) values (${"'"+ctx.request.body.brief+"'"},${"'"+ctx.request.body.title+"'"},${"'"+ctx.request.body.content+"'"},${ctx.request.body.num+1},${"'"+ctx.request.body.account+"'"})`;
  await query(sql);
})

router.put('/user/studyhistory',async (ctx,next)=>{
  let sql=`insert into userstudyhistory (history_title,userimg,username,time,excerpt,history_img,user_number,content) values (${"'"+ctx.request.body.title+"'"},${"'"+ctx.request.body.userimg+"'"},${"'"+ctx.request.body.username+"'"},${"'"+ctx.request.body.time+"'"},${"'"+ctx.request.body.excerpt+"'"},${"'"+ctx.request.body.historyimg+"'"},${"'"+ctx.request.body.account+"'"},${"'"+ctx.request.body.content+"'"})`;
  await query(sql);
})
//获取浏览历史
router.post('/user/gethistory',async (ctx,next)=>{
  let sql=`select * from userexamhistory where user_number=${ctx.request.body.account}`;
  let res=await query(sql);
  ctx.body=JSON.stringify(res.reverse());
})

router.post('/user/getstudyhistory',async (ctx,next)=>{
  let sql=`select * from userstudyhistory where user_number=${ctx.request.body.account}`;
  let res=await query(sql);
  ctx.body=JSON.stringify(res.reverse());
})

//获取消息
router.post('/user/getmessage',async (ctx,next)=>{
  let sql=`select key0 from examcircle where usernumber=${ctx.request.body.user_number}`;
  let res=await query(sql);
  let arr=[]
  res.map(item=>{
    arr.push(item.key0)
  })
  let sql1=`select * from usercoms where key0 in(${arr}) and username!=${"'"+ctx.request.body.username+"'"}`;
  let res1= await query(sql1);
  ctx.body=JSON.stringify(res1.reverse());
})

//获取消息详情页
router.post('/user/getmessagedetail',async (ctx,next)=>{
  let sql=`select * from examcircle where key0=${"'"+ctx.request.body.key0+"'"}`;
  let res=await query(sql);
  res.map(item=>{
    item.img=item.img.split(',')
    item.coms=item.coms.split(',')
  })
  ctx.body=JSON.stringify(res);
})
//圈子发布
router.put('/share/write',async (ctx,next)=>{
  let sql=`insert into examcircle (key0,username,userimg,content,time0,comnum,likenum,usernumber,img,coms,islike) values (${"'"+ctx.request.body.key0+"'"},${"'"+ctx.request.body.username+"'"},${"'"+ctx.request.body.userimg+"'"},${"'"+ctx.request.body.content+"'"},${"'"+ctx.request.body.time0+"'"},${ctx.request.body.comnum},${ctx.request.body.likenum},${"'"+ctx.request.body.usernumber+"'"},${"'"+ctx.request.body.img+"'"},${"'"+ctx.request.body.coms+"'"},${"'"+ctx.request.body.islike+"'"})`;
  await query(sql);
})

//圈子查询
router.get('/share/getshare',async (ctx,next)=>{
  let sql=`select * from examcircle`;
  let res=await query(sql);
  res.map(item=>{
    item.img=item.img.split(',')
    item.coms=item.coms.split(',')
  })
  ctx.body=JSON.stringify(res.reverse());
})

//喜欢数量
router.post('/share/islike',async (ctx,next)=>{
  let sql=`select * from userlike where user_number=${ctx.request.body.account}`;
  let res=await query(sql);
  
  let arr=[]
  res.map(item=>{
    if(item.user_number==ctx.request.body.account){
        arr.push(item.like_id);
    }
  })
  let sql1=`select * from examcircle`;
  let res1=await query(sql1)
  res1.map(item=>{
    item.img=item.img.split(',')
    item.coms=item.coms.split(',')
  })
  res.map(item1=>{
    res1.map(item2=>{
      if(item1.like_id==item2.key0){
        item2.islike=true;
      }
    })
  })
  ctx.body=JSON.stringify(res1.reverse());
})

//我的喜欢
router.put('/share/like',async (ctx,next)=>{
  let sql=`insert into userlike (like_id,user_number) values (${"'"+ctx.request.body.id+"'"},${"'"+ctx.request.body.account+"'"})`;
  await query(sql);
  let sql1=`update examcircle set likenum=${ctx.request.body.likenum+1} where key0=${"'"+ctx.request.body.id+"'"}`;
  await query(sql1);
})
//取消喜欢
router.put('/share/unlike',async (ctx,next)=>{
  console.log(ctx.request.body)
  let sql=`delete from userlike where like_id=${"'"+ctx.request.body.id+"'"}`;
  await query(sql);
  let sql1=`update examcircle set likenum=${ctx.request.body.likenum-1} where key0=${"'"+ctx.request.body.id+"'"}`;
  await query(sql1);
})

//添加评论
router.put('/share/coms',async (ctx,next)=>{
  console.log(ctx.request.body);
  let sql=`insert into usercoms (key0,username,userimg,content,time0) values(${"'"+ctx.request.body.key0+"'"},${"'"+ctx.request.body.username+"'"},${"'"+ctx.request.body.userimg+"'"},${"'"+ctx.request.body.content+"'"},${"'"+ctx.request.body.time0+"'"})`;
  await query(sql)
  let sql1=`select comnum from examcircle where key0=${"'"+ctx.request.body.key0+"'"}`;
  let res=await query(sql1);
  let sql2=`update examcircle set comnum=${res[0].comnum+1} where key0=${"'"+ctx.request.body.key0+"'"}`;
  await query(sql2);
})

//获取评论
router.post('/share/getcoms',async (ctx,next)=>{
  let sql=`select * from usercoms where key0=${"'"+ctx.request.body.key0+"'"}`;
  let res=await query(sql);
  ctx.body=JSON.stringify(res.reverse());
})

//获取我的喜欢
router.post('/user/getlike',async (ctx,next)=>{
  let sql=`select * from userlike where user_number=${ctx.request.body.account}`;
  let res=await query(sql);
  
  let arr=[]
  res.map(item=>{
    if(item.user_number==ctx.request.body.account){
        arr.push(item.like_id);
    }
  })
  let sql1=`select * from examcircle`;
  let res1=await query(sql1)
  res1.map(item=>{
    item.img=item.img.split(',')
  })
  let arr1=[];
  res.map(item1=>{
    res1.map(item2=>{
      if(item1.like_id==item2.key0){
        item2.islike=true;
        arr1.push(item2);
      }
    })
  })
  ctx.body=JSON.stringify(arr1.reverse());
})

//获取我的创作
router.post('/user/getwrite',async (ctx,next)=>{
  let sql=`select * from userlike where user_number=${ctx.request.body.account}`;
  let res=await query(sql);
  
  let arr=[]
  res.map(item=>{
    if(item.user_number==ctx.request.body.account){
        arr.push(item.like_id);
    }
  })
  let sql1=`select * from examcircle where usernumber=${ctx.request.body.account}`;
  let res1=await query(sql1)
  res1.map(item=>{
    item.img=item.img.split(',')
  })
  res.map(item1=>{
    res1.map(item2=>{
      if(item1.like_id==item2.key0){
        item2.islike=true;
      }
    })
  })
  ctx.body=JSON.stringify(res1.reverse());
})

//打卡
router.put('/home/check',async (ctx,next)=>{
  let sql=`select * from checkmes where user_number=${ctx.request.body.user_number}`;
  let res=await query(sql);
  let sql1=`insert into checkmes (check_id,checkuser_img,checkuser_name,check_topic,check_content,check_time,check_img,user_number) values (${"'"+ctx.request.body.check_id+"'"},${"'"+ctx.request.body.checkuser_img+"'"},${"'"+ctx.request.body.checkuser_name+"'"},'打卡第${res.length+1}天',${"'"+ctx.request.body.check_content+"'"},${"'"+ctx.request.body.check_time+"'"},${"'"+ctx.request.body.check_img+"'"},${"'"+ctx.request.body.user_number+"'"})`;
  await query(sql1)
})

//获得打卡
router.post('/home/getcheck',async (ctx,next)=>{
  let sql=`select * from checkmes where user_number=${"'"+ctx.request.body.account+"'"}`;
  let res=await query(sql);
  res.map(item=>{
    item.check_img=item.check_img.split(',')
  })
  ctx.body=JSON.stringify(res.reverse());
})

//获取单词
router.post('/home/getwords',async(ctx,next)=>{
    let sql=`select * from word  where id not in(select id from wordhistory where user_number=${"'"+ctx.request.body.user_number+"'"})`
    let res=await query(sql);
    ctx.body=JSON.stringify(res.slice(0,20));
})

//获取已背单词数量
router.post('/home/getwordnum',async(ctx,next)=>{
  let sql=`select * from wordhistory  where user_number=${"'"+ctx.request.body.user_number+"'"}`
  let res=await query(sql);
  ctx.body=JSON.stringify(res.length);
})
//添加单词
router.put('/home/putword',async (ctx,next)=>{
  let sql=`insert into wordhistory (id,user_number) values (${ctx.request.body.id},${"'"+ctx.request.body.user_number+"'"})`;
  await query(sql)
  
})

//图片转换
router.post('/pic',async(ctx,next) => {
  const file = ctx.request.body;
  const uri = file.uri;
  const time = Date.now();
  const fileName = time + uri.substring(uri.length-4);
  let base = file.base64;
  const filePath = path.join(__dirname,'img') + `\/${fileName}`;
  console.log('地址：',filePath);
  var dataBuffer = Buffer.from(base, 'base64')//新的用法 Buffer.from
  fs.writeFile(filePath, dataBuffer, function (err) {
    if (err) return
    // console.log('图片保存成功')
  })

  let realPath = `https://wx.qasdwer.top:8080/img/${fileName}`
  console.log('网络：',realPath);
  ctx.body = JSON.stringify({Type:200,status:'上传成功',addr:realPath})
})


router.get('/img/:name',async(ctx,next) => {
  let name = ctx.params.name;
  let filePath = `./img/${name}`
  let data = fs.readFileSync(filePath);
  let mimeType = mime.lookup(filePath);
  ctx.set('content-type',mimeType);
  ctx.body = data;
})
app.use(router.routes());

let serv = https.createServer({
  key: fs.readFileSync('/usr/local/share/wx.qasdwer.top.key'),
  cert: fs.readFileSync('/usr/local/share/wx.qasdwer.top.crt')
})



serv.on('request', app.callback())


serv.listen(8080,'0.0.0.0')


//app.listen(8080);
